//========================================================================
// *作者：#YSW#  邮箱：#AuthorEmail#
// *脚本：GameManager
// *时间：#CreateTime#
// *©2021 MYJL . All rights reserved.
//* ======================================================================

using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Text;
using UnityEngine;

namespace HDMCode.Excel
{
    public class ExcelToJson
    {
        /// <summary>
        /// 配置表工作流
        /// </summary>
        IWorkbook workBook;

        /// <summary>
        /// 配置表包含的Sheel
        /// </summary>
        List<string> sheels = new List<string>();

        /// <summary>
        /// 记录所有Sheel与ID的字典
        /// </summary>
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> sheel_ID;

        /// <summary>
        /// 记录ID与所有键值对的字典
        /// </summary>
        Dictionary<string, Dictionary<string, string>> iD_value;

        /// <summary>
        /// xslx路径
        /// </summary>
        private string excelPath { get; set; }

        /// <summary>
        /// 输出的Json
        /// </summary>
        public readonly string outJson;

        /// <summary>
        /// 输出的Json字节
        /// </summary>
        public readonly byte[] outJsonByte;

        /// <summary>
        /// 输出加密后的Json
        /// </summary>
        public readonly string outEncryptJson;

        /// <summary>
        /// 获取根据路径将Excel转换为Json
        /// </summary>
        /// <param name="excelPath"></param>
        public ExcelToJson(string excelPath)
        {
            this.excelPath = $"{Application.streamingAssetsPath}/{excelPath}.xlsx";
            outJson = ReadExcelToJson();
            outJsonByte = Encoding.UTF8.GetBytes(outJson);
        }

        /// <summary>
        /// 读取并输出Json
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private string ReadExcelToJson()
        {
            ///获取配置表
            if (excelPath.IndexOf(".xlsx") > 0)
                workBook = new XSSFWorkbook(excelPath);
            else
                return default;

            ///获取所有副表
            if (workBook.NumberOfSheets > 0)
            {
                sheel_ID = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();

                for (int i = 0; i < workBook.NumberOfSheets; i++)
                {
                    ///剔除以#号开头的副表
                    if (workBook.GetSheetName(i).Substring(0, 1) != "#")
                    {
                        sheels.Add(workBook.GetSheetName(i));
                    }
                }

                ///遍历配置表中的所有副表Sheel
                foreach (var item in sheels)
                {
                    ///获取当前Sheel
                    ISheet sheet = GetSheet(item);

                    ///获取索引对应的头部
                    GetIndexHead(sheet);

                    ///用于保存所有ID对应的所有键值对的字典
                    iD_value = new Dictionary<string, Dictionary<string, string>>();

                    ///遍历所有行
                    for (int i = 0; i <= GetColumnNum(sheet).row; i++)
                    {
                        ///跳过无效行：空数值的行、没有ID的行、配置表前三行
                        IRow sheet_row = sheet.GetRow(i);

                        if (sheet_row == null || sheet_row.GetCell(0) == null || i < 3)
                            continue;

                        ///用于保存当前行对应的所有键值对的字典
                        Dictionary<string, string> head_value = new Dictionary<string, string>();

                        ///当前行的ID
                        string id = sheet_row.GetCell(0).ToString();

                        if (string.IsNullOrEmpty(id))
                            continue;
                        ///遍历当前行的所有列，因为ID已经记录，跳过ID记录
                        //for (int b = 1; b < sheet_row.LastCellNum; b++)
                        for (int b = 1; b < index_Head.Count; b++)
                        {
                            ///将当前值绑定到头部
                            string head;
                            if (index_Head.TryGetValue(b.ToString(), out head))
                            {
                                try
                                {
                                    ///将键值对写入ID字典中
                                    if (string.IsNullOrEmpty(CheckTableCell(sheet_row, b)))
                                        DirCoverWrite(head_value, head, "Null");
                                    else
                                        DirCoverWrite(head_value, head, CheckTableCell(sheet_row, b));
                                }
                                catch (Exception error)
                                {
                                    throw new Exception($"写入头部{excelPath}--{sheet.SheetName}--{head}--当前行{i}失败！ {error}！");
                                }
                            }
                            else
                                Debug.LogWarning($"配置表{excelPath}/{sheet.SheetName}中未找到值【{CheckTableCell(sheet_row, b)}】对应的头部，将自动跳过");
                        }
                        ///将键值对与ID进行绑定，如果ID重复那么替换
                        DirCoverWrite(iD_value, id, head_value);
                    }
                    ///将ID与Sheet进行绑定
                    DirCoverWrite(sheel_ID, sheet.SheetName, iD_value);
                }
            }
            else
            {
                throw new Exception($"没有找到配置表 {excelPath} 包含的内容！");
            }
            return $"[{JsonConvert.SerializeObject(sheel_ID)}]";
        }

        /// <summary>
        /// 根据名称获取副表
        /// </summary>
        /// <param name="sheetName">副表名称</param>
        /// <returns></returns>
        /// <exception cref="Exception"> 获取副表异常 </exception>
        private ISheet GetSheet(string sheetName = null)
        {
            ISheet sheet;

            if (sheetName != null)
                sheet = workBook.GetSheet(sheetName);
            else
                sheet = workBook.GetSheetAt(0);

            if (sheet == null)
                throw new Exception($"没有找到指定的{sheetName}");

            return sheet;
        }

        ///获取第一行索引对应的头部
        Dictionary<string, string> index_Head;

        /// <summary>
        /// 获取当前Sheel的第一行，并绑定横列下标对应的头部
        /// </summary>
        /// <param name="sheet"> 副表Sheel </param>
        private void GetIndexHead(ISheet sheet)
        {
            ///用于保存当前Sheel下横列索引对应头部的字典
            index_Head = new Dictionary<string, string>();

            ///获取当前Sheel行列数
            (int row, int column) column = GetColumnNum(sheet);

            ///将横列索引与头部进行绑定
            for (int i = 0; i < column.column; i++)
            {
                IRow sheet_row = sheet.GetRow(0);
                ///判断单元格是否为空
                if (string.IsNullOrEmpty(CheckTableCell(sheet_row, i)))
                    continue;
                else
                    index_Head.Add(i.ToString(), CheckTableCell(sheet_row, i));
            }
        }

        /// <summary>
        /// 获取行中某一单元格的值
        /// </summary>
        /// <param name="cell"> 行 </param>
        /// <param name="index"> 行中的单元格下标 </param>
        /// <returns></returns>
        private string CheckTableCell(IRow cell, int index = 0)
        {
            IRow row = cell;

            if (row.GetCell(index) != null)
            {
                row.GetCell(index).SetCellType(CellType.String);

                if (string.IsNullOrEmpty(row.GetCell(index).ToString()))
                    return "";
                else
                    return row.GetCell(index).ToString();
            }
            else
                return "";
        }

        /// <summary>
        /// 字典的覆盖写入
        /// </summary>
        /// <typeparam name="T">Key类型</typeparam>
        /// <param name="keyValues">Vakue类型</param>
        private void DirCoverWrite<T, Y>(Dictionary<T, Y> keyValues, T key, Y value)
        {
            if (key == null)
            {
                Debug.Log("异常键");
                return;
            }

            if (value == null)
            {
                Debug.Log("异常值");
                return;
            }

            ///将键值对与ID进行绑定，如果ID重复那么替换
            if (keyValues.ContainsKey(key))
            {
                keyValues.Remove(key);
            }
            else
                keyValues.Add(key, value);
        }

        /// <summary>
        /// 获取配置表的行列数量
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public (int row, int column) GetColumnNum(ISheet sheet)
        {
            IRow firstRow = sheet.GetRow(0);
            if (firstRow == null)
            {
                return (0, 0);
            }
            else
            {
                (int row, int column) row_column = (sheet.LastRowNum, firstRow.LastCellNum);
                return row_column;
            }
        }

    }
}
